package xyz.keyon.dao.impl;

import xyz.keyon.bean.Student;
import xyz.keyon.dao.IStudentDao;
import xyz.keyon.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements IStudentDao {
    @Override
    public boolean addStudent(Student student) {
        //执行sql语句
        String sql = "INSERT INTO student(sno,sname,sage,saddress) VALUES(?,?,?,?)";
        Object[] params = {student.getSno(), student.getSname(), student.getSage(), student.getSaddress()};
        int count = JdbcUtils.executeUpdate(sql,params);
        //处理返回结果
        if(count > 0) {
            //增加成功
            return true;
        } else {
            //增加失败
            return false;
        }
    }

    @Override
    public boolean deleteStudentBySno(String Sno) {
        //执行sql语句
        String sql = "DELETE FROM student WHERE sno=?";
        Object[] params = {Sno};
        int count = JdbcUtils.executeUpdate(sql,params);
        //处理返回结果
        if(count > 0) {
            //删除成功
            return true;
        } else {
            //删除失败
            return false;
        }
    }

    @Override
    public boolean updateStudentBySno(Student student) {
        //执行sql语句
        String sql = "UPDATE student SET sname=?, sage=?, saddress=? WHERE sno=?;";
        Object[] params = {student.getSname(), student.getSage(), student.getSaddress(), student.getSno()};
        int count = JdbcUtils.executeUpdate(sql,params);
        //处理返回结果
        if(count > 0) {
            //更新成功
            return true;
        } else {
            //更新失败
            return false;
        }
    }

    @Override
    public List<Student> queryAllStudent() {
        ResultSet rs = null;
        try{
            //执行sql语句
            String sql = "SELECT * FROM student";
            rs = JdbcUtils.executeQuery(sql,null);
            //处理返回结果
            List<Student> students = new ArrayList<Student>();
            while(rs.next()) {
                Student student = new Student();
                student.setSno(rs.getString("sno"));
                student.setSname(rs.getString("sname"));
                student.setSage(rs.getInt("sage"));
                student.setSaddress(rs.getString("saddress"));
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(rs,null,null);
        }
        return null;
    }

    @Override
    public Student queryStudentBySno(String Sno) {
        try{
            //执行sql语句
            String sql = "SELECT * FROM student WHERE sno=?";
            Object [] params = {Sno};
            ResultSet rs = JdbcUtils.executeQuery(sql,params);
            //处理返回结果
            if(rs.next()) {
                Student student = new Student();
                student.setSno(rs.getString("sno"));
                student.setSname(rs.getString("sname"));
                student.setSage(rs.getInt("sage"));
                student.setSaddress(rs.getString("saddress"));
                return student;
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int getTotalCount() {
        String sql = "SELECT count(1) FROM student";
        return JdbcUtils.getTotalCount(sql);
    }

    @Override
    public List<Student> queryStudentsByPage(int currentPage, int pageSize) {
        ResultSet rs = null;
        //执行sql语句
        String sql = "SELECT * FROM student LIMIT ?,?";
        Object[] params = {(currentPage - 1) * pageSize, pageSize};
        rs = JdbcUtils.executeQuery(sql, params);
        //处理返回结果
        List<Student> students = new ArrayList<Student>();
        try {
            while (rs.next()) {
                Student student = new Student(
                        rs.getString("sno"),
                        rs.getString("sname"),
                        rs.getInt("sage"),
                        rs.getString("saddress")
                );
                students.add(student);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(rs,null,null);
        }
        return students;
    }
}
